package org.maggioni.springsandbox.rehearsals.e06jdbc.e02supportclasses.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.maggioni.springsandbox.rehearsals.e06jdbc.e02supportclasses.model.Job;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class NamedJdbcDaoImpl extends NamedParameterJdbcDaoSupport {
	
	public List<Job> getAllJobsBetweenSalary(int min, int max) {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs where min_salary>=:min and max_salary<=:max";
		SqlParameterSource namedSqlParameter = new MapSqlParameterSource()
														.addValue("min", min)
														.addValue("max", max);
		return getNamedParameterJdbcTemplate().query(sql, namedSqlParameter, new JobMapper());
	}


	private static final class JobMapper implements RowMapper<Job> {
		@Override
		public Job mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new Job(
					resultSet.getString("job_id"),
					resultSet.getString("job_title"),
					resultSet.getInt("min_salary"),
					resultSet.getInt("max_salary")
					);
		}
	}
	
	

}
